#!/usr/bin/perl
use strict;
use XML::LibXML;
use Config::IniFiles;
use LWP::UserAgent;
use DBI;
use Time::Local;
use HTTP::Date;
my $ua = LWP::UserAgent->new((timeout => 100));

my $cfg=Config::IniFiles->new( -file => '/var/myscheduler/dbConfig.ini');
my $db_name="rcoos";
my $tname=$cfg->val('rcoos','tname');
my $db_user=$cfg->val('rcoos','username');
my $db_passwd=$cfg->val('rcoos','password');

my $dbh = DBI->connect ("dbi:Pg:dbname=$db_name","$db_user","$db_passwd");
if (!defined $dbh) {die "Cannot connect to database!\n";}

# Target INSERT SQL.
my $insert_sql = "INSERT INTO $tname ("
  ." row_entry_date"
  .",row_update_date"
  .",platform_handle"
  .",sensor_id"
  .",m_type_id"
  .",m_date"
  .",m_lon"
  .",m_lat"
  .",m_z"
  .",m_value"
  .") VALUES";

my @url_list = (
#SC
'http://waterdata.usgs.gov/sc/nwis/current?result_md=1&result_md_minutes=60&format=rdb&multiple_site_no=02110815,02176585,321603080432202,02176603,02176611,02176635,02176589,02176640,02172020,02172050,021720698,02172080,02172002,02172040,02175000,02172053,021720677,021720709,02171639,021720710'
,'http://waterdata.usgs.gov/sc/nwis/current?result_md=1&result_md_minutes=60&format=rdb&multiple_site_no=02172084,02110760,02110805,02110809,02135200,02110777,02110704,02110802,02110770,02110755,021108125,02110725',
'http://waterdata.usgs.gov/nc/nwis/current?result_md=1&result_md_minutes=60&format=rdb&multiple_site_no=0208455155,0208455560,02092162,0209265810,0208453300,0208114150,0209262905,02084472,02081022,02081094,02108690'
);


foreach my $url (@url_list) {

   # get the page
   #thought about using the multi-station request, but there's a 20 station limit which creates some setup work on the request end - just getting all for one state at a time for now
    #print "$url\n";
    my $response = $ua->get($url);

    # die If we timeout, but keep going if we 404
    if (!$response->is_success && !($response->status_line =~ /404/)) {
        die $response->status_line.' : '.$url;
    }
    
    my $depth = 'NULL';

    my @d = sort split(/\n/,$response->content);
    my ($fixed_longitude,$fixed_latitude,$time_zone);
    
    foreach my $l (@d) {

         #print $l;

	  if ($l =~ / EST/) {
             $time_zone=5;
          }
	  if ($l =~ / EDT/) {
             $time_zone=4;
          }


	  if ($l =~ /^USGS/) {

	    my ($usgs,$platform_name,$dd_nu,$parameter_code,$date,$t,$measurement) = split(/\t/,$l);
            

            #print "measure =$measurement\n";


            $date=get_gmt_time($date,$time_zone);
            #print "**$date\n";   	    
	    $date =~ s/ /T/g;
            $date="'$date'";
	    #print "**$date\n";
           
            my ($obsType,$uomType,$found,$conversion) = split(/\:/,&get_observed_property($parameter_code));
	    if ($found eq 'found') {

	      if ($conversion) {
		  $conversion =~ s/var1/$measurement/g;
		   #note the use of eval and that we're trusting the input from the source data here and considering potential security issues
		   $measurement = eval "sprintf($conversion)";
		   
	      }

              
              if($obsType ne "depth"){
              
                 my $sql="select sensor.row_id,m_type.row_id,platform.platform_handle,platform.fixed_latitude,platform.fixed_longitude from sensor
                  left join m_type on m_type.row_id=sensor.m_type_id
                  left join m_scalar_type on m_type.m_scalar_type_id=m_scalar_type.row_id
                  left join obs_type on obs_type.row_id=m_scalar_type.obs_type_id
                  left join uom_type on uom_type.row_id=m_scalar_type.uom_type_id
                  left join platform on platform.row_id=sensor.platform_id
                  where platform.short_name='$platform_name' and obs_type.standard_name='$obsType' and uom_type.standard_name='$uomType'"; 
                  my $sth = $dbh->prepare($sql);
                 
                  $sth->execute();
                  while (my ($sensor_id,$m_type_id,$platform_handle,$latitude,$longitude) = $sth->fetchrow_array)  {
                 
                    print "$insert_sql(current_timestamp,current_timestamp,'$platform_handle',$sensor_id,$m_type_id,$date,$longitude,$latitude,$depth,$measurement);\n";
                  }
                  
                  $sth->finish();
                 
                      
                 
              }#end of if ne depth
              else{
                 my $sql="select platform.platform_handle from platform
                  where platform.short_name='$platform_name' "; 
                  my $sth = $dbh->prepare($sql);

                  $sth->execute();
                  while (my ($platform_handle) = $sth->fetchrow_array)  {
                    
                    print "update $tname set m_z=$measurement where platform_handle='$platform_handle' and m_date=$date;\n";
                  }
                  $sth->finish();
              }
	    }#end of found
	    else { print "#notfound:$obsType:$uomType\n"; }

	  }  #if USGS
     } #foreach line 

} #foreach url


$dbh->disconnect();

exit 0;

###########################################

sub get_gmt_time{
 my ($dat,$dsttime)=@_;
 
 my $t=str2time($dat);
 my ($sec,$min,$hour,$mday,$mon,$year,$twday,$yday,$isdst) = localtime($t+$dsttime*3600);
     $year=$year+1900;
     $mon=$mon+1;
     if($mon<10){
        $mon="0".$mon;
     }
    if($mday<10){
       $mday="0".$mday;
    }
    if($sec<10){
       $sec="0".$sec;
    }
   if($min<10){
     $min="0".$min;
   }
   if($hour<10){
     $hour="0".$hour;
   }
   $dat=$year."-".$mon."-".$mday." ".$hour.":".$min.":".$sec;
   
   return $dat;
}

sub get_observed_property {

my $string = shift;

if ($string eq '00300') { $string = 'oxygen_concentration:mg_L-1:found'}
elsif ($string eq '00301') { $string = 'oxygen_concentration:percent:found'}
elsif ($string eq '00010') { $string = 'water_temperature:celsius:found'}
elsif ($string eq '00011') { $string = 'water_temperature:celsius:found:"%.2f",(var1-32)*5/9'} #convert fahrenheit to celsius
elsif ($string eq '00095') { $string = 'water_conductivity:mS_cm-1:found'}
elsif ($string eq '00003') { $string = 'depth:feet:found:"%.2f",var1*.3048'} #convert feet to meters
elsif ($string eq '00076') { $string = 'turbidity:ntu:found'}
elsif ($string eq '63680') { $string = 'turbidity:ntu:found'}  #actually units 'nu'
elsif ($string eq '00065') { $string = 'gage_height:m:found:"%.2f",var1*.3048'} #convert feet to meters
elsif ($string eq '00400') { $string = 'ph:units:found'}

elsif ($string eq '00055') { $string = 'stream_velocity:ft_s-1:found'}
elsif ($string eq '00060') { $string = 'discharge:cubic_ft_s-1:found'}

elsif ($string eq '00025') { $string = 'air_pressure:millibar:found:"%.2f",var1*1.333224'} #convert mmHg to mb
elsif ($string eq '00052') { $string = 'relative_humidity:percent:found'}
elsif ($string eq '00020') { $string = 'air_temperature:celsius:found'}
elsif ($string eq '00021') { $string = 'air_temperature:celsius:found:"%.2f",(var1-32)*5/9'} #convert fahrenheit to celsius
elsif ($string eq '00035') { $string = 'wind_speed:m_s-1:found:"%.2f",var1*0.447'} #convert mph to m_s-1
elsif ($string eq '00036') { $string = 'wind_from_direction:degrees_true:found'}
elsif ($string eq '62608') { $string = 'solar_radiation:watts_per_m^2:found'}
elsif ($string eq '00096') { $string = 'salinity:mg_per_milliliter:found'}
elsif ($string eq '00480') { $string = 'salinity:psu:found'}  #actual units 'ppt'

else { $string .= '::notfound'; }

#print $string."\n";
return $string;


=comment
http://nwis.waterdata.usgs.gov/usa/nwis/pmcodes?pm_group=ALL&pm_search=&format=html_table&show=parameter_cd&show=parameter_group_nm&show=parameter_nm

 00090	physical property	Oxidation reduction potential, millivolts
 72020	physical property	Elevation above NGVD 1929, feet 
 74207	physical property	Moisture content, soil, volumetric, percent of total volume 

 00025	physical property	Barometric pressure, millimeters of mercury
 00052	physical property	Relative humidity, percent
 00020	physical property	Temperature, air, degrees Celsius 
 00021	physical property	Temperature, air, degrees Fahrenheit
 00035	physical property	Wind speed, miles per hour
 00036	physical property	Wind direction, degrees clockwise from true north
 62608	physical property	Total solar radiation (direct + diffuse radiation on a horizontal surface), watts per square meter 
 00096	physical property	Salinity, water, unfiltered, milligrams per milliliter at 25 degrees Celsius
 
 72019	physical property	Depth to water level, feet below land surface
 00062	physical property	Elevation of reservoir water surface above datum, feet
=cut

}
